//
//  DBOperation.m
//  LFCWarmSystem
//
//  Created by 李福才 on 16/5/4.
//  Copyright © 2016年 李福才. All rights reserved.
//

#import "DBOperation.h"

@implementation DBOperation
@synthesize selfdb;
@synthesize dbQueue;

static DBOperation *dbOperateInstance = nil;
//獲得db操作實例
+(id)getDBOperateInstance{
    @try{
        @synchronized(self)
        {
            if (dbOperateInstance == nil)
                dbOperateInstance = [[DBOperation alloc] initialWithDataBase];
        }
        return dbOperateInstance;
    }
    @catch (NSException *exception) {
        NSLog(@"DBOperation ERROR: %@", exception.description);
        return nil;
    }
    
}
-(id)initialWithDataBase{
    self=[super init];
    if (self){
        //        self.db=[FMDatabase databaseWithPath:USER_DB_PATH];
        //        [self.db open];
        dbQueue = [FMDatabaseQueue databaseQueueWithPath:USER_DB_PATH];
        selfdb = [FMDatabase databaseWithPath:USER_DB_PATH];
        //        [self.dbQueue inDatabase:^(FMDatabase *tmpdb) {
        //            self.selfdb=tmpdb;
        //        }];
    }
    return self;
}
+(BOOL)checkDataBase{
    //更新DB版本
    [self updateDBSchema];
    
    //Open DataBase
    NSString *file=USER_DB_PATH;
    NSLog(@"Database Path:%@",USER_DB_PATH);
    sqlite3 *database;
    if(sqlite3_open([file UTF8String],&database)!=SQLITE_OK){
        NSLog(@"Failed to open database with message '%s'",sqlite3_errmsg(database));
        sqlite3_close(database);
        return FALSE;
    }
    sqlite3_close(database);
    return TRUE;
    
}

+(void)updateDBSchema{
    if (dbOperateInstance != nil)
    {
        [dbOperateInstance release];
        dbOperateInstance=nil;
    }
    NSString *plistPath = [[NSBundle mainBundle] pathForResource:@"DBOperation" ofType:@"plist"];
    NSMutableDictionary *data = [[NSMutableDictionary alloc] initWithContentsOfFile:plistPath];
    NSString *latestVersion=[data objectForKey:@"lastVersion"];
    NSLog(@"latestVersion %@",latestVersion);
    
    NSArray* versionArray=[[data allKeys] sortedArrayUsingSelector:@selector(compare:)];
    DBOperation *dbo=[DBOperation getDBOperateInstance];
    NSString *currentVersion=[dbo getCurrentDBVersion];
    if ([[currentVersion substringToIndex:1] isEqualToString:@"V"]) {
        NSString* newCurrentVersion = [currentVersion substringFromIndex:1];
        currentVersion =newCurrentVersion;
    }
    NSLog(@"currentVersion %@",currentVersion);
    
    if ([currentVersion doubleValue]<[latestVersion doubleValue]) {
        NSLog(@"versionArray %@",versionArray);
        for (NSString *version in versionArray) {
            if (![version isEqualToString:@"lastVersion"]) {
                NSArray* updateSqlArray=[data objectForKey:version];
                NSLog(@"updateSqlVersion %@",version);
                NSLog(@"updateSqlArray %@",updateSqlArray);
                
                for (NSString* updateSql in updateSqlArray) {
                    [dbo changeDBSchema:updateSql];
                }
            }
        }
        NSString *updateDB=[NSString stringWithFormat:@"update DB_Version set version='%@';",latestVersion];
        [dbo changeDBSchema:updateDB];
    }
    [data release];
}


//查詢當前DB版本號
-(NSString *)getCurrentDBVersion{
    @try {
        __block NSString *res=nil;
        NSString *sql=@"select version from DB_Version;";
        
        [self.dbQueue inDatabase:^(FMDatabase *db) {
            FMResultSet *rsType=[db executeQuery:sql];
            while ([rsType next]) {
                res=[rsType stringForColumn:@"version"];
            }
            [rsType close];
        }];
        return res;
    }
    @catch (NSException *exception) {
        NSLog(@"DBOperation ERROR: %@", exception.description);
        NSLog(@"DB ERROR: %@ ",exception.description);
        return nil;
    }
}
//執行更新語句
-(BOOL)changeDBSchema:(NSString *)sql{
    @try {
        if (sql==nil||[@"" isEqualToString:sql]) {
            return NO;
        }
        [self.dbQueue inDatabase:^(FMDatabase *db) {
            [db executeUpdate:sql];
        }];
        return YES;
    }
    @catch (NSException *exception) {
        NSLog(@"DBOperation ERROR: %@", exception.description);
        //        NSLog(@"ERROR: %d - %@", db.lastErrorCode, db.lastErrorMessage);
        return NO;
    }

}





-(BOOL)insertPhoneWithModel:(ModelMainPhone *)model{
    @try {
        __block BOOL res = NO;
        [dbQueue inDatabase:^(FMDatabase *dbo) {
            NSString *sqlRoom=@"select * from main_phone where phonrNum=?;";
            FMResultSet *rsType=[dbo executeQuery:sqlRoom,model.phoneNumber];
            
            while ([rsType next]) {
                res = YES;
            }
            [rsType close];
            
            if (!res) {
                NSString *sqlInsert=@"insert into main_phone (phoneName ,phoneNum) values(?,?);";
                res = [dbo executeUpdate:sqlInsert,model.phoneNumber,model.phoneName];
            }else{
                NSString *sqlUpdate=@"update main_phone  set phoneName=?;";
                res = [dbo executeUpdate:sqlUpdate,model.phoneName];
            }
        }];
        
        return res;
    }
    @catch (NSException *exception) {
        NSLog(@"DBOperation ERROR: %@", exception.description);
        return NO;
    }
}
-(BOOL)deletePhoneWithModel:(ModelMainPhone *)model{
    @try {
        __block BOOL res = NO;
        [dbQueue inDatabase:^(FMDatabase *dbo){
            NSString *sqlRoom=@"delete from main_phone where phonrNum=?;";
            res = [dbo executeUpdate:sqlRoom,model.phoneNumber];
        }];
        return res;
    }
    @catch (NSException *exception) {
        NSLog(@"DBOperation ERROR: %@", exception.description);
        return NO;
    }
    return YES;
}
-(BOOL)updatePhoneWithModel:(ModelMainPhone *)model{
    @try {
        __block BOOL res = NO;
        [dbQueue inDatabase:^(FMDatabase *dbo){
            NSString *sqlUpdate=@"update main_phone  set phoneName=?;";
            res = [dbo executeUpdate:sqlUpdate,model.phoneName];
        }];
        return res;
    }
    @catch (NSException *exception) {
        NSLog(@"DBOperation ERROR: %@", exception.description);
        return NO;
    }
    
    return YES;
}
-(NSMutableArray*)selectPhoneAll{
    @try {
        __block NSMutableArray *mutableArr = [NSMutableArray array];
        [dbQueue inDatabase:^(FMDatabase *dbo){
            NSString *sqlRoom=@"select * from main_phone";
            FMResultSet *rsType=[dbo executeQuery:sqlRoom];
            while ([rsType next]){
                ModelMainPhone *model = [[ModelMainPhone alloc] init];
                model.phoneNumber = [rsType stringForColumn:@"phoneNum"];
                model.phoneName = [rsType stringForColumn:@"phoneName"];
                [mutableArr addObject:model];
            }
        }];
        return mutableArr;
    }
    @catch (NSException *exception) {
        NSLog(@"DBOperation ERROR: %@", exception.description);
        return nil;
    }
    
}


@end
